﻿using Se.Data;
using Se.Log;
using Se.Reflection;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Se.Model.Data
{
    /// <summary>
    /// 数据库管理器
    /// </summary>
    public class DBManager
    {
        private Dictionary<string, DBTable> Tables;

        public DBProviderBase Provider;
        

        public DBManager(DBProviderBase provider)
        {
            this.Provider = provider;
            this.Tables = new Dictionary<string, DBTable>();
        }

        /// <summary>
        /// 尝试添加DBTable，如果已经存在，返回false，否则返回true
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public bool TryAddTable(Type type)
        {
            if (this.Tables.ContainsKey(type.FullName))
            {
                return false;
            }

            var dbTable = this.CreateDBTable(type);
            Tables[type.FullName] = dbTable;
            return true;
        }

        public T GetEntity<T>(long id) where T : DataEntityBase
        {
            var name = typeof(T).FullName;
            var table = this.Tables[name];
            //TODO 此处可用存储过程
            var cmdText = "SELECT TOP 1 * FROM dbo." + table.DBTableName + " WHERE ID = @ID";
            var parameterID = this.Provider.CreateParameter("@ID", id);
            IDataParameter[] parameters = new[] { parameterID };
            var row = this.Provider.ExecuteDataRow(System.Data.CommandType.Text, cmdText, parameters);
            if (row == null) return null;

            var entity = ConvertToEntity<T>(row, table);
            return entity;
        }

        /// <summary>
        /// 获取所有的Entity
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public T[] GetEntity<T>() where T : DataEntityBase
        {
            var name = typeof(T).FullName;
            var table = this.Tables[name];
            //TODO 此处可用存储过程
            var cmdText = "SELECT * FROM dbo." + table.DBTableName + " ;";
            var dataSet = this.Provider.ExecuteDataSet(System.Data.CommandType.Text, cmdText, null);
            var entitySet = ConvertToEntity<T>(dataSet.Tables[0], table);

            return entitySet;
        }

        /// <summary>
        /// 返回Sql查询中第一行第一列的数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object GetDBScalar(string sql)
        {
            return this.Provider.ExecuteScalar(CommandType.Text, sql);
        }

        /// <summary>
        /// 转换为DataEntity
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public T ConvertToEntity<T>(DataRow row, DBTable dbTable) where T : DataEntityBase
        {
            var dataEntity = this.CreateDataEntity(Type.GetType(dbTable.ClassFullName));
            // 执行PreLoadData 方法
            dbTable.PreLoadDataAccesser.Invoke(dataEntity);

            foreach (var accesserItem in dbTable.DBPropertyAccesser)
            {
                object val = row[accesserItem.Key];
                bool isDbNull = row[accesserItem.Key].GetType().Equals(typeof(DBNull));
                var dbType = dbTable.Columns[accesserItem.Key].Type;
                if (dbType == typeof(Int16) || dbType == typeof(Int32) || dbType == typeof(Int64) || dbType == typeof(UInt16)
                    || dbType == typeof(UInt32) || dbType == typeof(UInt64) || dbType == typeof(Single)
                    || dbType == typeof(Double) || dbType == typeof(Boolean))
                {
                    if (isDbNull)
                        val = 0;
                }
                else if (dbType == typeof(DateTime))
                {
                    if (isDbNull)
                        val = DateTime.MinValue;
                }
                else if (dbType.Equals(typeof(String)))
                {
                    if (isDbNull)
                        val = null;
                }
                else
                {
                    //采用Newtonsoft序列化Json
                    if (isDbNull) val = "";
                    val = string.IsNullOrEmpty(row[accesserItem.Key].ToString()) ? null : Newtonsoft.Json.JsonConvert.DeserializeObject(row[accesserItem.Key].ToString());
                }

                accesserItem.Value.SetValue(dataEntity, val);
            }


            dataEntity.SaveType = DBSaveType.Update;

            // 执行AfterLoadData方法
            dbTable.AfterLoadDataAccesser.Invoke(dataEntity);

            return (T)dataEntity;
        }

        /// <summary>
        /// 转换为DataEntity数组
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public T[] ConvertToEntity<T>(DataTable table, DBTable dbTable) where T : DataEntityBase
        {
            List<T> entities = new List<T>();
            foreach (DataRow row in table.Rows)
            {
                var entity = this.ConvertToEntity<T>(row, dbTable);
                entities.Add(entity);
            }

            return entities.ToArray();
        }

        private string GetSaveSql(DataEntityBase entity, DBTable table, string postfix = "")
        {
            var sbSql = new StringBuilder();
            var propertyNames = table.DBPropertyAccesser.Keys.ToList();

            //删除
            if (entity.IsDeleted)
            {
                sbSql.Append("DELETE FROM dbo.").Append(table.DBTableName).Append(" WHERE ID = @ID").Append(postfix).Append(";");
                return sbSql.ToString();
            }
            if (entity.SaveType == DBSaveType.Insert)
            {
                sbSql.Append("INSERT INTO dbo.").Append(table.DBTableName).Append(" (");
                StringBuilder sbTemp = new StringBuilder();
                propertyNames.ForEach(name =>
                {
                    sbSql.Append(this.Provider.FormatName(name)).Append(",");
                    sbTemp.Append("@").Append(name).Append(postfix).Append(",");
                });
                sbSql.Remove(sbSql.Length - 1, 1);
                sbTemp.Remove(sbTemp.Length - 1, 1);
                sbSql.Append(")VALUES(").Append(sbTemp).Append(");");
            }
            else
            {
                sbSql.Append("UPDATE ").Append(table.DBTableName).Append(" SET ");
                propertyNames.ForEach(name =>
                {
                    sbSql.Append(name).Append("=@").Append(name).Append(postfix).Append(",");
                });
                sbSql.Remove(sbSql.Length - 1, 1);
                // TODO主键，目前默认ID为唯一主键
                sbSql.Append(" WHERE ID = @ID").Append(";");
            }

            return sbSql.ToString();
        }

        /// <summary>
        /// 默认的时间（最小的Time）
        /// </summary>
        readonly static DateTime DEFAULT_TIME = new DateTime(1970, 1, 1);

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="table"></param>
        /// <param name="postfix"></param>
        /// <returns></returns>
        private List<IDataParameter> GetSaveParameters(DataEntityBase entity, DBTable table, string postfix = "")
        {
            var parameters = new List<IDataParameter>();
            if (entity.IsDeleted)
            {
                var sqlParameter = this.Provider.CreateParameter("ID" + postfix, entity.ID);
                parameters.Add(sqlParameter);
                return parameters;
            }
            foreach (var accesserItem in table.DBPropertyAccesser)
            {
                var val = accesserItem.Value.GetValue(entity);
                //NULL值处理
                var dbType = table.Columns[accesserItem.Key].Type;
                if (dbType == typeof(Int16) || dbType == typeof(Int32) || dbType == typeof(Int64) || dbType == typeof(UInt16)
                    || dbType == typeof(UInt32) || dbType == typeof(UInt64) || dbType == typeof(Double)
                    || dbType == typeof(Single) || dbType == typeof(String) || dbType == typeof(Boolean))
                {
                    if (val == null)
                        val = 0;
                }
                else if (dbType == typeof(DateTime))
                {
                    if ((DateTime)val < DEFAULT_TIME)
                        val = DEFAULT_TIME;

                }
                else
                {
                    //采用Newtonsoft序列化Json
                    val = val == null ? string.Empty : Newtonsoft.Json.JsonConvert.SerializeObject(val);
                }

                var sqlParameter = this.Provider.CreateParameter(accesserItem.Key + postfix, val);
                parameters.Add(sqlParameter);
            }
            return parameters;
        }

        const int C_SaveNumbers = 100;

        /// <summary>
        /// 保存集合
        /// </summary>
        /// <param name="type"></param>
        /// <param name="collection"></param>
        /// <returns></returns>
        public bool SaveEntityCollection(Type type, out List<long> deletedIDs, params DataEntityBase[] collection)
        {
            deletedIDs = new List<long>();
            var typeName = type.FullName;
            var table = this.Tables[typeName];
            StringBuilder sbSql = new StringBuilder();
            List<IDataParameter> parameters = new List<IDataParameter>();

            //TODO 每100条数据做一次存储,要加锁


            for (var i = 0; i < collection.Length; i++)
            {
                var entity = collection[i];
                if (entity.IsDirty)
                {
                    sbSql.Append(GetSaveSql(entity, table, i.ToString()));
                    parameters.AddRange(GetSaveParameters(entity, table, i.ToString()));
                    //执行PreSave方法
                    table.PreSaveDataAccesser.Invoke(entity);
                    if (entity.IsDeleted)
                        deletedIDs.Add(entity.ID);
                }
            }
            if (sbSql.ToString().Trim() == string.Empty)
            {
                SeLog.Info("DBManager->SaveEntityCollection:没有需要保存的数据");
                return true;
            }
            //保存
            try
            {
                var result = this.Provider.ExecuteTransactionQuery("tran" + type.Name, CommandType.Text, sbSql.ToString(), parameters.ToArray());
                if (!result.IsSuccess)
                {
                    SeLog.Error("DBManager->SaveEntity errorinfo {0}", result.Message);
                    return false;
                }

                //执行AfterSave方法
                foreach (var entity in collection)
                {
                    table.AfterSaveDataAccesser.Invoke(entity);
                    entity.IsDirty = false;
                }
                return true;
            }
            catch (Exception ex)
            {
                SeLog.Error("DBManager->SaveEntity tableName:{0} errorinfo:{1}", table.DBTableName, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 删除DB表中所有数据
        /// </summary>
        /// <param name="dbTableName"></param>
        /// <returns></returns>
        public void ClearDBEntity(string dbTableName)
        {
            string sql = "DELETE FROM dbo." + dbTableName;
            try
            {
                this.Provider.ExecuteQuery(CommandType.Text, sql);
            }
            catch (Exception ex)
            {
                SeLog.Error("DBManager->ClearDBEntity:{0}", ex.ToString());
            }
        }

        /// <summary>
        /// 创建DBTable
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private DBTable CreateDBTable(Type type)
        {
            DBTable table = new DBTable(type.FullName, type.Name);

            table.DBPropertyAccesser = new Dictionary<string, IPropertyAccessor>();
            table.AllPropertyAccesser = new Dictionary<string, IPropertyAccessor>();
            foreach (var property in type.GetProperties())
            {
                var accesser = FastReflectionFactories.PropertyAccessorFactory.Create(property);
                var attributes = property.GetCustomAttributes(typeof(EntityFieldAttribute), false);
                if (attributes.Length > 0)
                {
                    table.DBPropertyAccesser.Add(property.Name, accesser);
                    var column = ((EntityFieldAttribute)attributes[0]).ToDBColumn(property.PropertyType);
                    if (string.IsNullOrEmpty(column.Name))
                    {
                        column.Name = property.Name;
                    }
                    table.Columns.Add(property.Name, column);
                }
                table.AllPropertyAccesser.Add(property.Name, accesser);
            }

            //MemberList
            table.PreLoadDataAccesser = FastReflectionFactories.MethodInvokerFactory.Create(type.GetMethod("PreLoadData"));
            table.AfterLoadDataAccesser = FastReflectionFactories.MethodInvokerFactory.Create(type.GetMethod("AfterLoadData"));
            table.PreSaveDataAccesser = FastReflectionFactories.MethodInvokerFactory.Create(type.GetMethod("PreSaveData"));
            table.AfterSaveDataAccesser = FastReflectionFactories.MethodInvokerFactory.Create(type.GetMethod("AfterSaveData"));

            //创建数据库表
            DBColumn[] columns;
            if (!this.Provider.CheckTable(table.DBTableName, out columns))
            {
                this.Provider.CreateTable(type.Name, table.Columns.Values.ToArray());
            }
            // TODO检查字段是否有错误

            return table;
        }


        /// <summary>
        /// 创建新的DataEntityBase(默认是Inert)
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private DataEntityBase CreateDataEntity(Type type)
        {
            var dataEntity = System.Activator.CreateInstance(type, DBSaveType.Insert);
            return (DataEntityBase)dataEntity;
        }
    }
}
